﻿Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

Public Class WebForm2
    Inherits System.Web.UI.Page


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Check if the page is PostBack, in that case fill the System list with items from DB
        If Page.IsPostBack Then
            FillList()
        End If

    End Sub

    'pull information from db and populate listbox
    Private Sub FillList()
        'Connection variable
        Dim connectionLink As New DBConnect()
        'Open a connection
        Dim connOpen As New SqlConnection
        connOpen = connectionLink.getConnection()
        'Create SQL command string
        Dim sqlcmd As String
        If SystemDropDown.SelectedIndex <> -1 Then
            sqlcmd = "select attribute.AttributeID, attribute.AttributeName from attribute INNER JOIN (Select attributeID from (select * from system_attribute where systemID = '" & _
                SystemDropDown.Items.Item(SystemDropDown.SelectedIndex).Value.ToString & _
                "') as sys_attrib1 where attributeID = sys_attrib1.attributeID) as tabAttrib ON attribute.attributeID = tabAttrib.attributeID"
        Else
            sqlcmd = "select attribute.AttributeID, attribute.AttributeName from attribute INNER JOIN (Select attributeID from (select * from system_attribute where systemID = '" & _
                SystemDropDown.Items.Item(SystemDropDown.SelectedIndex).Value.ToString & "') as sys_attrib1 where attributeID = sys_attrib1.attributeID) as tabAttrib ON attribute.attributeID = tabAttrib.attributeID"
        End If

        'Display system name
        systemNameModifyTxt.Text = SystemDropDown.SelectedItem.Text.ToString

        Dim sqlall As String = "Select * from Attribute"
        'Create a SQL Command Variable
        Dim connReader As SqlDataReader
        'Run Query
        Try
            'Return Reader
            connReader = connectionLink.getDataReader(sqlcmd)
            'Read output for system box
            While connReader.Read()
                Dim listItem As New ListItem(connReader("AttributeName"), connReader("AttributeID"))
                systemAttributeBox.Items.Add(listItem)
            End While
            'Read all attributes
            connReader.Close()
            connReader = connectionLink.getDataReader(sqlall)
            'Retrieve all data
            If availableAttributeBox.Items.Count = 0 Then
                While connReader.Read()
                    Dim listItemAll As New ListItem(connReader("AttributeName"), connReader("AttributeID"))
                    availableAttributeBox.Items.Add(listItemAll)
                    AvailableAttribList.Items.Add(listItemAll)
                End While
            End If
            'Close Connection Reader
            connReader.Close()
        Catch ex As Exception
        End Try
    End Sub

    'Clear out SystemAttribute List 
    Sub selectIndexChanged()
        RefreshList()
    End Sub

    'remove all items from listbox
    Private Sub RefreshList()
        'Clear list and fill out with items
        systemAttributeBox.Items.Clear()
        FillList()
    End Sub

    Sub test()
        MsgBox(systemNameModifyTxt.Text.ToString)
    End Sub

    'Show add System Multiview
    Sub saveChangesModify(ByVal sender As Object, _
                     ByVal e As EventArgs)
        'Connection variable
        Dim connectionLink As New DBConnect()
        'Open a connection
        Dim connOpen As New SqlConnection
        connOpen = connectionLink.getConnection()

        'Create SQL command string
        Dim sqlcmd As SqlCommand = New SqlCommand
        sqlcmd.CommandText = "UPDATE System SET SystemName='" & systemNameModifyTxt.Text.ToString & _
            "' WHERE SystemID='" & SystemDropDown.SelectedItem.Value.ToString & "'"
        'Create a SQL Command Variable
        Try
            'Execute Update Query
            If connectionLink.doUpdate(sqlcmd.CommandText) = -1 Then
                MsgBox("Problem with Updating the Record", MsgBoxStyle.Information)
            End If
        Catch ex As Exception

        End Try
        SystemDropDown.DataBind()
        SystemAttributesList.DataBind()
        systemAttributeBox.DataBind()
        AvailableAttribList.DataBind()
        availableAttributeBox.DataBind()
        removeDropDownLst.DataBind()
    End Sub


    'Move method which executes the move between boxes
    Sub moveAttribute(ByVal direction As String, ByVal action As String)
        'Add Items to System Attribute
        If direction = "left" Then
            If action = "modify" Then
                If availableAttributeBox.GetSelectedIndices.Length > 0 Then
                    'Insert Attribute into database
                    insertAttribute()
                    RefreshList()
                End If
                'Add items to system attributes in ADD multiview
            ElseIf action = "add" Then
                If AvailableAttribList.GetSelectedIndices.Length > 0 Then
                    SystemAttributesList.Items.Add(AvailableAttribList.SelectedItem)
                    RefreshList()
                End If
            End If
            'Remove Items from System Attributes
        ElseIf direction = "right" Then
            If action = "modify" Then
                'Delete Attribute
                deleteAttribute(systemAttributeBox.SelectedValue.ToString)
                RefreshList()
            ElseIf action = "add" Then
                For i As Integer = 0 To systemAttributeBox.Items.Count - 1
                    SystemAttributesList.Items.Remove(SystemAttributesList.SelectedItem)
                    RefreshList()
                Next
            End If
            End If
    End Sub

    'Insert attribute into System Attribute ListBox
    Sub insertAttribute()
        'Connection Variables
        Dim connectLink As DBConnect = New DBConnect()
        Dim SqlCommand As SqlCommand = New SqlCommand
        If systemAttributeBox.Items.Contains(availableAttributeBox.SelectedItem) = False Then
            SqlCommand.CommandText = "INSERT INTO System_Attribute (SystemID, AttributeID) " & _
                           "VALUES ( '" & SystemDropDown.Items.Item(SystemDropDown.SelectedIndex).Value.ToString & "' ," & _
                         " '" & availableAttributeBox.Items.Item(availableAttributeBox.SelectedIndex).Value.ToString & "' )"
            If connectLink.doUpdate(SqlCommand.CommandText.ToString) > -1 Then
                systemAttributeBox.Items.Add(availableAttributeBox.SelectedItem)
            Else
                MsgBox("Error Occured. Please make sure attribute is not already exists in 'System attributes' table")
            End If
        Else
            MsgBox("It appears that this combination of System and Attribute already exist")
        End If
    End Sub
    'Deleting Attribute
    Sub deleteAttribute(ByVal attributeToDelete As String)
        'Connection Variables
        Dim connectLink As DBConnect = New DBConnect()
        Dim SqlCommand As SqlCommand = New SqlCommand
        'Delete Item from the System_Attribute table
        SqlCommand.CommandText = "DELETE FROM System_Attribute WHERE SystemID =" & _
                     " '" & SystemDropDown.Items.Item(SystemDropDown.SelectedIndex).Value.ToString & "' AND " & _
                   " AttributeID ='" & attributeToDelete & "' "
        'Check if item was deleted from database
        If connectLink.doUpdate(SqlCommand.CommandText.ToString) > -1 Then
            systemAttributeBox.Items.Remove(systemAttributeBox.SelectedItem)
        Else
            MsgBox("This attribute is dependant on Goal. Please delete appropriate Goal first. Would you like to delete the Goal?", MsgBoxStyle.YesNo, vbVerticalTab)
        End If
        'Clean up the list
        systemAttributeBox.Items.Clear()
        FillList()
    End Sub

    'Add Save
    Sub saveAdd(ByVal sender As Object, _
                     ByVal e As EventArgs)
        'Connection Variables
        Dim connectLink As DBConnect = New DBConnect()
        Dim SqlCommand As SqlCommand = New SqlCommand
        Dim SqlCommandAttrib As SqlCommand = New SqlCommand
        Dim count As Integer = 0
        Dim idReturn As Integer = 0
        SqlCommand.CommandText = "INSERT INTO System (SystemName) " & _
                       "VALUES ( '" & systemNameAddTxt.Text.ToString & "' )"
        'Returns the last inserted ID
        idReturn = connectLink.doUpdate2(SqlCommand.CommandText.ToString)
        If idReturn > -1 Then
            'Insert Attribute into System_Attribute Table
            While count < SystemAttributesList.Items.Count
                SqlCommandAttrib.CommandText = "INSERT INTO System_Attribute (SystemID, AttributeID) " & _
                          "VALUES ( '" & idReturn & "','" & SystemAttributesList.Items.Item(count).Value & "' )"

                count = count + 1
                If connectLink.doUpdate(SqlCommandAttrib.CommandText.ToString) > -1 Then
                Else
                    MsgBox("Error Occured. Please make sure attribute is not already exists in 'System attributes' table")
                End If
            End While
        End If
    End Sub

    Sub deleteSysSQL()
        Dim response As MsgBoxResult
        Dim SqlCommand As SqlCommand = New SqlCommand
        Dim sqlCommandAttr As SqlCommand = New SqlCommand
        Dim sqlDeleteGameSystemAttrib As SqlCommand = New SqlCommand
        Dim sqlDeleteGameSystem As SqlCommand = New SqlCommand
        Dim connectLink As DBConnect = New DBConnect()
        response = MsgBox("Confirm Deletion?", MsgBoxStyle.OkCancel, "Please confirm...")
        If response = MsgBoxResult.Ok Then
            sqlCommandAttr.CommandText = "DELETE FROM System_Attribute WHERE SystemID = '" & removeDropDownLst.SelectedValue & "'"
            sqlDeleteGameSystemAttrib.CommandText = "DELETE FROM Game_SystemAttribute WHERE SystemId = '" & _
                removeDropDownLst.SelectedValue & "'"
            sqlDeleteGameSystem.CommandText = "DELETE FROM Game_System WHERE SystemID = '" & _
                removeDropDownLst.SelectedValue & "'"
            SqlCommand.CommandText = "DELETE FROM System WHERE SystemID = '" & removeDropDownLst.SelectedValue & "'"
            'Delete from Game_System
            connectLink.doUpdate(sqlDeleteGameSystem.CommandText)
            'Delete from Game_SystemAttribute
            connectLink.doUpdate(sqlDeleteGameSystemAttrib.CommandText)
            'Delete System Attribute
            connectLink.doUpdate(sqlCommandAttr.CommandText)
            'Delete System
            connectLink.doUpdate(SqlCommand.CommandText)
            'Check if items was deleted
            If connectLink.doUpdate(SqlCommand.CommandText.ToString) > -1 Then
                removeDropDownLst.Items.Remove(removeDropDownLst.SelectedItem)
            Else
                MsgBox("This System attributes associated with it.Would you like to delete the System and De-associate attributes?", MsgBoxStyle.YesNo, vbVerticalTab)
                If response = MsgBoxResult.Yes Then

                End If
            End If
        End If
        SystemDropDown.DataBind()
        SystemAttributesList.DataBind()
        systemAttributeBox.DataBind()
        AvailableAttribList.DataBind()
        availableAttributeBox.DataBind()
        removeDropDownLst.DataBind()
    End Sub
End Class